/*
	EXEC sp_transit_report_proc '0','0','0','0','0','0','','0','0','0','XLREPORT','0'
	SELECT * FROM SHIPPED_LOT_HISTORY
*/
ALTER PROC sp_transit_report_proc

@PROCESS_TYPE		VARCHAR(50),
@PROCESSSTEP_IDS	VARCHAR(100),
@BASEPRODUCT_ID		VARCHAR(50),
@VENDOR			    VARCHAR(50),
@FROMDATE		    VARCHAR(50),
@TODATE 		    VARCHAR(50),
@SEARCH_PARTNUMBER	VARCHAR(50),
@LOTCATEGORY		VARCHAR(50),
@STATUS			    VARCHAR(50),
@REPORT_FROM		VARCHAR(50),
@REPORT_TYPE		VARCHAR(50),
@DEFAULT_PROMPT		VARCHAR(50)

AS
BEGIN

CREATE TABLE #TRANSIT_TABLE  ( 	[SHIP DATE] DATETIME, [SHIP QTY] INT, [SHIP DIE QTY] INT, [STATUS] VARCHAR(50), [SO NUMBER] VARCHAR(50), [PO NUMBER] VARCHAR(50),
				[INVOICE NO] VARCHAR(50),[MAWB NO] VARCHAR(50),[HAWB NO] VARCHAR(50), [ETD] DATETIME, [ETA] DATETIME,
				[WAFER ID] VARCHAR(200), [FLIGHT NO] VARCHAR(50),[NEXT LOCATION] VARCHAR(100), [CUSTOMER SO] VARCHAR(50),
				[LOT NUMBER] VARCHAR(50),[FAB LOT NUMBER] VARCHAR(50), [LOT CATEGORY] VARCHAR(50),
				[PART NUMBER] VARCHAR(50),[PS TYPE] VARCHAR(50),[PS TYPE NAME] VARCHAR(50),[PREV LOCATION] VARCHAR(50),
				[BASE PRODUCT] VARCHAR(50),[PS TYPE ID] INT,[PACKAGE TYPE] VARCHAR(50), [SHIP REQ NUMBER] VARCHAR(50), [DATE CODE] VARCHAR(20) )

DECLARE @SQLSTRING1	NVARCHAR(4000)
DECLARE @SQLSTRING	NVARCHAR(4000)

DECLARE @TEMP_VARCHAR 	VARCHAR(50)
DECLARE @START_DATE 	DATETIME
DECLARE @END_DATE 	DATETIME

	SET @SQLSTRING1 = N' INSERT INTO #TRANSIT_TABLE
	select 
		SL.SHIP_DATE,SL.SHIP_QTY,SL.SHIP_GOOD_DIE_QTY, SL.STATUS,SHOP_ORDER_NO,ISNULL(SL_CUSTOMER_PO,PO_NUMBER),SL_INVOICE_NUMBER,SL_MAWB_NUMBER,SL_HAWB_NUMBER,SL_ETD,SL_ETA,WAFER_ID,
		SL_FLIGHT_NUMBER,SL_NEXTLOCATION,SL_CUSTOMER_SO,LOT_NUMBER,FAB_LOT_NUMBER,LOT_CATEGORY,PART_NUMBER,PS_TYPE,PS_TYPE_NAME,L_VENDOR_NAME,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END) AS BASEPRODUCTNAME, 
			(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS_TYPE) AS Ps_type_Id,
		(CASE
			WHEN PS_TYPE = ''ASSEMBLY'' THEN 	( SELECT TOP 1 PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''Package ID'' AND PROCESSSTEP_ID = LT.PROCESSSTEP_ID )
			/*WHEN ( PS_TYPE = ''FT'' OR PS_TYPE = ''QA'' OR PS_TYPE = ''FG'') THEN  ( SELECT TOP 1 PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''Package ID'' AND
						PROCESSSTEP_ID = ( SELECT TOP 1 PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID( LT.PROCESSSTEP_ID ) WHERE PS_TYPE = ''ASSEMBLY'' ) )*/
			ELSE NULL
		END) AS Package_Type, SHIP_REQ_NO, LT.LOT_DATE_CODE '

	SET @SQLSTRING = ''
	
	IF @REPORT_FROM = 'WIP'
	BEGIN
		IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
		BEGIN
			SET @TEMP_VARCHAR = CONVERT(VARCHAR, @FROMDATE, 101)+' 12:00AM'
			SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
			SET @TEMP_VARCHAR = NULL
			SET @TEMP_VARCHAR = CONVERT(VARCHAR, @FROMDATE, 101)+' 11:59PM'
			SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
			
			SET @SQLSTRING = @SQLSTRING +' from 
				shipped_lot_HISTORY SL, LOT_TABLE LT, PRODUCT P,PROCESS_STEP PS, LOCATION L
			WHERE 
				SL.LOT_ID = LT.LOT_ID
				AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND LT.LOCATION_ID = L.LOCATION_ID 
				AND SL.STATUS = ''Transit'' AND SHIP_STATUS = 0 
				AND ( SL.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''') '
		END
		ELSE
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' from 
				shipped_lot SL, LOT_TABLE LT, PRODUCT P,PROCESS_STEP PS, LOCATION L
			WHERE 
				SL.LOT_ID = LT.LOT_ID
				AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND LT.LOCATION_ID = L.LOCATION_ID 
				AND SL.STATUS = ''Transit'' AND SHIP_STATUS = 0 '
		END
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING +' from 
			shipped_lot SL, LOT_TABLE LT, PRODUCT P,PROCESS_STEP PS, LOCATION L
		WHERE 
			SL.LOT_ID = LT.LOT_ID
			AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LT.LOCATION_ID = L.LOCATION_ID '
			--AND SL.STATUS = ''Transit'' AND SHIP_STATUS = 0 
		
		IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' AND SL.SHIP_DATE >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
		END
	END
	
	
	IF ( @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + 'AND PS.PS_TYPE = ''' +  @PROCESS_TYPE + '''' 
	END

	IF ( @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.PROCESSSTEP_ID = ''' +  @PROCESSSTEP_IDS + '''' 
		--SET @SQLSTRING = @SQLSTRING + ' AND LT.PROCESSSTEP_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PROCESSSTEP_IDS +'''))'
	END 

	IF ( @BASEPRODUCT_ID IS NOT NULL AND @BASEPRODUCT_ID <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P_PARENT_PRODUCT_ID =  ''' +  @BASEPRODUCT_ID + '''' 
	END
	
	IF ( @VENDOR IS NOT NULL AND ( @VENDOR <> '0' and @VENDOR <> 'All Vendors') )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' and L_VENDOR_NAME =  ''' + @VENDOR + '''' 
	END

	IF ( @LOTCATEGORY IS NOT NULL AND  @LOTCATEGORY <> '0'  )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' and LT.LOT_CATEGORY =  ''' + @LOTCATEGORY + '''' 
	END

	IF ( @STATUS IS NOT NULL AND  @STATUS <> '0'  )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' and SL.STATUS =  ''' + @STATUS + '''' 
	END

	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + +' AND SL.SHIP_DATE <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
		
	--PRINT @SQLSTRING1 + @SQLSTRING

	EXEC(@SQLSTRING1 + @SQLSTRING)
	
	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
		IF @REPORT_FROM = 'WIP'
		BEGIN
			SELECT 
				[BASE PRODUCT],[PART NUMBER],[FAB LOT NUMBER],[LOT NUMBER],[PS TYPE],[PREV LOCATION] AS [FROM VENDOR],
			    [SHIP DATE],[SHIP QTY],[SHIP DIE QTY],[NEXT LOCATION],[DATE CODE], [SHIP REQ NUMBER],[PO NUMBER],[CUSTOMER SO] AS [SO NUMBER],[INVOICE NO],
				[LOT CATEGORY],[MAWB NO],[HAWB NO],[FLIGHT NO],[ETD],[ETA],[WAFER ID]
			FROM
				#TRANSIT_TABLE
			ORDER BY
				[BASE PRODUCT],[PART NUMBER],[PS TYPE ID],[PREV LOCATION],[SHIP DATE]
		END
		ELSE
		BEGIN
			SELECT 
				[BASE PRODUCT],[PART NUMBER],[FAB LOT NUMBER],[LOT NUMBER],[PS TYPE],[PREV LOCATION] AS [FROM VENDOR],
				[SHIP DATE],[SHIP QTY],[SHIP DIE QTY],[NEXT LOCATION],[DATE CODE],[SHIP REQ NUMBER],[PO NUMBER],[CUSTOMER SO] AS [SO NUMBER],[INVOICE NO],
				[LOT CATEGORY],[MAWB NO],[HAWB NO],[FLIGHT NO],[ETD],[ETA],[WAFER ID],[STATUS]				
			FROM
				#TRANSIT_TABLE ORDER BY [BASE PRODUCT],[PART NUMBER],[PS TYPE ID],[PREV LOCATION],[SHIP DATE]
		END
	END
	ELSE
	BEGIN
		
		SELECT 
			[SHIP DATE],[SHIP QTY],[STATUS],[SO NUMBER],[SHIP REQ NUMBER],[PO NUMBER],[INVOICE NO],[MAWB NO],[HAWB NO],[ETD],
			[ETA],[WAFER ID],[FLIGHT NO],[NEXT LOCATION],[CUSTOMER SO],[LOT NUMBER],[FAB LOT NUMBER],
			[LOT CATEGORY],[PART NUMBER],[PS TYPE],[PS TYPE NAME],[PREV LOCATION],
			[BASE PRODUCT],[PS TYPE ID],[PACKAGE TYPE]
		FROM
			#TRANSIT_TABLE
	END
END